﻿Imports System.Data.SqlClient
Public Class TermManagement
    Inherits System.Web.UI.Page

    Dim con As New DBConnect
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'check if user signed in if not going back to login page 
        If Not Session.Item("UserIn").Equals("yes") Then
            Response.Redirect("default.aspx")
        End If

        If Not (IsPostBack) Then
            Dim SqlConnectString As String = con.SqlConnectString
            Dim myConnection As New SqlConnection(SqlConnectString)
            Dim myCommand As New SqlCommand()
            ' Dim result As Integer
            ' *** [Richie]: SATS._Default3.startTermID = 1
            myCommand.Connection = myConnection
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.CommandText = "SelectTermYears"       'Name of the Stored Procedure to execute

            Try
                myConnection.Open()
                ddDepartments.DataSource = myCommand.ExecuteReader()
                ddDepartments.DataValueField = "TermID"
                ddDepartments.DataTextField = "TermYear"

                ddDepartments.DataBind()

            Catch ex As Exception
                Throw ex
            Finally
                myConnection.Close()
                myConnection.Dispose()
            End Try

            Dim SqlConnectString1 As String = con.SqlConnectString
            Dim myConnection1 As New SqlConnection(SqlConnectString1)
            Dim myCommand1 As New SqlCommand()
            ' Dim result As Integer
            ' *** [Richie]: SATS._Default3.startTermID = 1
            myCommand1.Connection = myConnection1
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommand1.CommandType = CommandType.StoredProcedure
            myCommand1.CommandText = "SelectTermYears"       'Name of the Stored Procedure to execute

            Try
                myConnection1.Open()
                ddDepartments0.DataSource = myCommand1.ExecuteReader()
                ddDepartments0.DataValueField = "TermID"
                ddDepartments0.DataTextField = "TermYear"

                ddDepartments0.DataBind()

            Catch ex As Exception
                Throw ex
            Finally
                myConnection1.Close()
                myConnection1.Dispose()
            End Try


            ' getting current start year and assinging it to label
            Dim SqlConnectString2 As String = con.SqlConnectString
            Dim myConnection2 As New SqlConnection(SqlConnectString2)
            Dim myCommand2 As New SqlCommand()
            myCommand2.Connection = myConnection2
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommand2.CommandType = CommandType.StoredProcedure
            myCommand2.CommandText = "GetStartYear"       'Name of the Stored Procedure to execute
            Dim returnParameter2 As New SqlParameter("@TermYear", 0)
            returnParameter2.Direction = ParameterDirection.Output
            myCommand2.Parameters.Add(returnParameter2)

            Try
                myConnection2.Open()
                lblStartYear.Text = myCommand2.ExecuteScalar()
            Catch ex As Exception
                Throw ex
            Finally
                'lblStartYear.Text = myCommand2.Parameters("@new_identity").Value
                'lblStartYear.Text = CType(myCommand2.Parameters("@new_identity").Value, String)
                myConnection2.Close()
                myConnection2.Dispose()
            End Try

            ' getting current end year and assinging it to label
            Dim SqlConnectString3 As String = con.SqlConnectString
            Dim myConnection3 As New SqlConnection(SqlConnectString3)
            Dim myCommand3 As New SqlCommand()
            myCommand3.Connection = myConnection3
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommand3.CommandType = CommandType.StoredProcedure
            myCommand3.CommandText = "GetEndYear"       'Name of the Stored Procedure to execute
            Dim returnParameter3 As New SqlParameter("@TermYear", 0)
            returnParameter3.Direction = ParameterDirection.Output
            myCommand3.Parameters.Add(returnParameter3)

            Try
                myConnection3.Open()
                lblEndYear.Text = myCommand3.ExecuteScalar()
                'lblEndYear.Text = CType(myCommand3.Parameters("@TermYear").Value, String)

            Catch ex As Exception
                Throw ex
            Finally
                myConnection3.Close()
                myConnection3.Dispose()
            End Try

        End If

    End Sub

    Protected Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        Dim SqlConnectString As String = con.SqlConnectString
        Dim myConnection As New SqlConnection(SqlConnectString)
        Dim myCommand As New SqlCommand()
        myCommand.Connection = myConnection
        'Set the SQLCommand object's properties for executing a Stored Procedure
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.CommandText = "SetStartYear"       'Name of the Stored Procedure to execute
        Dim inputParameter As New SqlParameter("@startYearID", CInt(ddDepartments.SelectedItem.Value))
        inputParameter.Direction = ParameterDirection.Input
        myCommand.Parameters.Add(inputParameter)

        Try
            myConnection.Open()
            myCommand.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            myConnection.Close()
            myConnection.Dispose()
        End Try

        Dim SqlConnectString1 As String = con.SqlConnectString
        Dim myConnection1 As New SqlConnection(SqlConnectString1)
        Dim myCommand1 As New SqlCommand()
        myCommand1.Connection = myConnection1
        'Set the SQLCommand object's properties for executing a Stored Procedure
        myCommand1.CommandType = CommandType.StoredProcedure
        myCommand1.CommandText = "SetEndYear"       'Name of the Stored Procedure to execute
        Dim inputParameter1 As New SqlParameter("@endYearID", CInt(ddDepartments0.SelectedItem.Value))
        inputParameter1.Direction = ParameterDirection.Input
        myCommand1.Parameters.Add(inputParameter1)

        Try
            myConnection1.Open()
            myCommand1.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            myConnection1.Close()
            myConnection1.Dispose()
        End Try

        Response.Redirect("TermManagement.aspx")

    End Sub
   

End Class